# 定时任务

import os
import re
import time
import datetime

import MySQLdb
import schedule
from dingtalkchatbot.chatbot import DingtalkChatbot

# 开发数据库
conn = MySQLdb.Connect(host='127.0.0.1', port=3306, user='root', passwd='mysql', db='log_analysis', charset='utf8')
# 测试数据库
# conn = MySQLdb.Connect(host='192.168.1.238', port=3306, user='root', passwd='123456', db='log_analysis', charset='utf8')

cursor = conn.cursor()

# 访问日志正则
access_log_pattern = '''([\d\.]{7,}) - - \[([\w\:\/]+) \+[\d]+\] "([\w]+) (\/[\S]*) ([\S]+)" (\d+) (\d+) "([^"]+)" "([^"]+)"'''
access_pattern = re.compile(access_log_pattern)

# 错误日志正则
error_log_pattern = '''([\d\/]{10} [\d\:]{8}) \[([^ ]+)]\ [^ ]+ [^ ]+ ([\S ]+), [^ ]+ ([\d.]+), [^ ]+ ([\w.]+), [^ ]+ "([^ ]+) ([\S]+) ([\S]+)", [\S ]+ "[\S]+", [\w]{8}: "([\S]+)"'''
error_pattern = re.compile(error_log_pattern)

# Windows 日志文件绝对路径
directory = 'F:/Office/Project/Gitee/Log_Analysis/logs/wwwlogs/'


# Ubuntu 日志文件绝对路径
# directory = '/www/wwwlogs/'


def read_logs(log_name):
    """
    连续读取日志文件, 每次读取默认时长
    """
    print("正在读取日志文件! {}".format(log_name))

    with open(directory + log_name, 'rb') as f:  # 一定要用'rb', 因为seek是以bytes来计算的
        # 通过日志名称取到对应站点的信息
        site_id, site_name, host = get_site_data(log_name)

        # 新增日志参数记录
        log_param_to_mysql(log_name, site_name)

        # ping站点域名, 判断通不通
        result = ping_link(host)
        if result:
            # result=True, 域名连通
            if 'error' in log_name:  # 错误日志
                # 获取日志文件当前参数信息
                error_count, error_time, remind_time, position = get_log_param(log_name)
                # 实时读取错误日志
                read_error_log(f, log_name, site_id, site_name, error_count, error_time, remind_time, position)
            else:  # 访问日志
                position = get_log_param(log_name)
                # 实时读取访问日志
                read_access_log(f, log_name, site_id, position)
            # 统计日志数据, 存储数据库
            overview_data_to_mysql(log_name)
            # 查询数据库日志文件, 统计接口访问量数据, 存储数据库
            get_table_data_to_mysql(log_name)
            # 访问量, 访客量, 存储数据库
            pv_uv_data_to_mysql(log_name)
        else:
            # result=False, 域名不通

            # msg = "{} 域名连接异常!".format(host)
            # print(msg)

            msg = "站点报错提醒: \n域名: {} \n域名连接异常, 请及时处理! ".format(host)
            # print(msg)
            current_time = datetime.datetime.now()
            # cur_time = "'" + current_time.strftime("%Y-%m-%d") + "'"
            cur_time = current_time.strftime("%Y-%m-%d")

            mobiles = []
            sql = "select phone from tb_duty where duty_date='%s'" % (cur_time,)
            try:
                cursor.execute(sql)
                phones = cursor.fetchall()
                mobiles = [mobile[0] for mobile in phones]
            except Exception as e:
                print(e)
            # print(mobiles)
            ding_remind(msg, mobiles)


def read_access_log(f, log_name, site_id, position):
    """
    读取访问日志文件
    """
    print("上次读取结束位置: {}".format(position))

    end_point = f.seek(0, 2)  # 获得文件末尾位置
    f.seek(0, 0)  # 返回文件初始位置
    print("日志文件末尾位置: {}".format(end_point))

    if end_point < position:
        start_point = 0
    elif end_point > position:
        start_point = position  # 每次读取错误日志的开始位置
    else:
        print("{} 日志文件读取结束!".format(log_name))
        return
    print("本次读取开始位置: {}".format(start_point))

    f.seek(start_point, 1)  # 移动文件读取指针到指定位置
    time1 = datetime.datetime.now()  # 读取日志开始时间

    # 遍历日志文件
    for line in f:
        content = line.decode()
        match = access_pattern.match(content)
        if not match:
            continue

        # 访问日志数据存储数据库
        access_log_to_mysql(match, site_id)

        time2 = datetime.datetime.now()  # 读取日志结束时间
        delta = time2 - time1  # 读取日志时间间隔

        position = f.tell()  # 日志文件读取的当前位置

        if delta.seconds > 10 * 60:  # 日志读取时长 默认5分钟
            # 若读取时间大于指定日志读取时长, 停止读取
            # print("当前位置: {}".format(position))
            position_to_mysql(log_name, position)
            break
        else:
            # 若读取时间小于或等于日志读取时长, 继续读取
            # 若当前位置和末尾位置相同, 则表示日志文件读取结束, 记录当前位置, 跳出循环
            if position == end_point:
                print("{} 日志文件读取结束!".format(log_name))
                position_to_mysql(log_name, position)
                break
            # 若当前位置和末尾位置不同, 则表示日志文件还未读完, 继续循环读取
            else:
                continue


def read_error_log(f, log_name, site_id, site_name, error_count, error_time, remind_time, position):
    """
    读取错误日志文件
    """
    count = 0  # 报错次数统计
    url_temp = ''
    url_dict = {}
    time_temp = datetime.datetime.now()
    ding_tag = False  # 钉钉是否提醒标识

    print("上次读取结束位置: {}".format(position))

    end_point = f.seek(0, 2)  # 获得文件末尾位置
    f.seek(0, 0)  # 返回文件初始位置
    print("日志文件末尾位置: {}".format(end_point))

    if end_point < position:
        start_point = 0
    elif end_point > position:
        start_point = position  # 每次读取错误日志的开始位置
    else:
        print("{} 日志文件读取结束!".format(log_name))
        return
    print("本次读取开始位置: {}".format(start_point))

    f.seek(start_point, 1)  # 移动文件读取指针到指定位置
    time1 = datetime.datetime.now()  # 读取日志开始时间

    # 遍历日志文件
    for line in f:
        content = line.decode()
        match = error_pattern.match(content)
        if not match:
            continue

        time2 = datetime.datetime.now()  # 读取日志结束时间

        date_time = datetime.datetime.strptime(match.group(1), '%Y/%m/%d %H:%M:%S')
        message = match.group(3)
        message = message.replace("\'", "`")
        host = match.group(5)
        url = match.group(7)

        error_log_to_mysql(match, site_id)  # 错误日志存储数据库

        log_delta = date_time - time_temp  # 错误日志时间差
        if 0 <= log_delta.seconds <= 60 * error_time and url == url_temp:
            # 错误日志时间差内, 相同url报错次数累加
            count += 1

            # 钉钉未预警, 报错次数大于等于报错次数阈值
            if not ding_tag and count >= error_count:
                if url not in url_dict.keys():
                    msg = "站点报错提醒: \n报错站点: {},\n报错时间: {},\n报错域名: {},\n报错接口: {},\n报错信息: {}".format(
                        site_name, date_time, host, url, message)
                    # print(msg)
                    current_time = datetime.datetime.now()
                    cur_time = current_time.strftime("%Y-%m-%d")

                    # 若 当前时间 - 站点报错时间 < 定时任务时间间隔, 则钉钉提醒
                    if (current_time - date_time).seconds < 60 * 60:
                        # print(current_time - date_time)
                        mobiles = []
                        sql = "select phone from tb_duty where duty_date='%s'" % (cur_time,)
                        try:
                            cursor.execute(sql)
                            phones = cursor.fetchall()
                            mobiles = [mobile[0] for mobile in phones]
                        except Exception as e:
                            print(e)

                        # print(msg, mobiles)
                        ding_remind(msg, mobiles)
                    else:
                        #  否则只存储数据库, 钉钉群机器人不提醒
                        print("错误日志已过时, 钉钉不提醒!")

                    # 报错提醒数据存储数据库
                    params = (site_name, date_time, host, url, message, site_id)
                    remind_to_mysql(params)
                    url_dict[url] = date_time
                    ding_tag = True
                elif url in url_dict.keys():
                    ding_delta = date_time - url_dict[url]  # 相同接口预警时间差
                    if ding_delta.seconds > 60 * remind_time:
                        msg = "站点报错提醒: \n报错站点: {},\n报错时间: {},\n报错域名: {},\n报错接口: {},\n报错信息: {}".format(
                            site_name, date_time, host, url, message)
                        # print(msg)

                        current_time = datetime.datetime.now()
                        cur_time = current_time.strftime("%Y-%m-%d")

                        # 若 当前时间 - 站点报错时间 < 定时任务时间间隔, 则钉钉提醒
                        if (current_time - date_time).seconds < 60 * 60:
                            mobiles = []
                            sql = "select phone from tb_duty where duty_date='%s'" % (cur_time,)
                            try:
                                cursor.execute(sql)
                                phones = cursor.fetchall()
                                mobiles = [mobile[0] for mobile in phones]
                            except Exception as e:
                                print(e)
                            # print(msg, mobiles)
                            ding_remind(msg, mobiles)
                        else:
                            # 否则只存储数据库, 钉钉群机器人不提醒
                            print("错误日志已过时, 钉钉不提醒!")

                        # 报错提醒数据存储数据库
                        params = (site_name, date_time, host, url, message, site_id)
                        remind_to_mysql(params)
                        url_dict[url] = date_time
                        ding_tag = True
        else:
            # print("重新计数")
            time_temp = date_time
            url_temp = url
            count = 0
            ding_tag = False

        delta = time2 - time1  # 读取日志时间间隔
        position = f.tell()  # 日志文件读取的当前位置

        if delta.seconds > 10 * 60:  # 日志读取时长 默认5分钟
            # 若读取时间大于指定日志读取时长, 停止读取
            # print("当前位置: {}".format(position))
            position_to_mysql(log_name, position)
            break
        else:
            # 若读取时间小于或等于日志读取时长, 继续读取
            # 若当前位置和末尾位置相同, 则表示日志文件读取结束, 记录当前位置, 跳出循环
            if position == end_point:
                print("{} 日志文件读取结束!".format(log_name))
                position_to_mysql(log_name, position)
                break
            # 若当前位置和末尾位置不同, 则表示日志文件还未读完, 继续循环读取
            else:
                continue


def access_log_to_mysql(match, site_id):
    """
    访问日志数据存储数据库
    """
    ip = match.group(1)
    date_time = time.strftime('%Y-%m-%d %H:%M:%S',
                              time.strptime(match.group(2), '%d/%b/%Y:%H:%M:%S'))  # 日期时间统一化
    method = match.group(3)
    url = match.group(4)
    protocol = match.group(5)
    status = int(match.group(6))
    size = round((float(match.group(7)) / 1024), 2)  # 字节单位b转换为kb
    referrer = "" if match.group(8) == '-' else match.group(8)  # 来源页面为空判断存储方式
    useragent = "" if match.group(9) == '-' else match.group(9)  # 设备信息为空判断存储方式

    params = (ip, date_time, method, url, protocol, status, size, referrer, useragent, site_id)

    sql = """
    insert into tb_access_log(ip, date_time, method, url, protocol, status, size, referrer, useragent, site_id) 
    values ('%s', '%s', '%s', '%s', '%s', %d, %.2f, '%s', '%s', %d)
    """ % params

    try:
        cursor.execute(sql)
        conn.commit()
    except Exception as e:
        print(e)
        conn.rollback()


def error_log_to_mysql(match, site_id):
    """
    错误日志数据存储数据库
    """
    date_time = datetime.datetime.strptime(match.group(1), '%Y/%m/%d %H:%M:%S')  # 日期时间统一化
    level = match.group(2)
    message_temp = match.group(3)
    message = message_temp.replace("\'", "`")  # 防止错误信息字符串与mysql语句冲突, 将"'"转换为"`"
    ip = match.group(4)
    host = match.group(5)
    method = match.group(6)
    url = match.group(7)
    protocol = match.group(8)
    referrer = '' if match.group(9) == '-' else match.group(9)  # 来源页面为空判断存储方式

    params = (date_time, level, message, ip, host, method, url, protocol, referrer, site_id)

    sql = """
    insert into tb_error_log(date_time, level, message, ip, host, method, url, protocol, referrer, site_id) 
    values ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %d)
    """ % params

    try:
        cursor.execute(sql)
        conn.commit()
    except Exception as e:
        print(e)
        conn.rollback()


def log_param_to_mysql(log_name, site_name):
    """
    日志参数存储数据库
    """
    sql = "select * from tb_param where log_name='%s'" % (log_name,)
    log_param = None
    try:
        cursor.execute(sql)
        log_param = cursor.fetchone()
    except Exception as e:
        print(e)

    if not log_param:
        read_time = 5
        crontab_time = 1
        position = 0
        if 'error' in log_name:
            error_count = 10
            error_time = 15
            remind_time = 30
            params = (log_name, site_name, error_count, error_time, remind_time, read_time, crontab_time, position)

            sql = """
            insert into tb_param(log_name, site_name, error_count, error_time, remind_time, read_time, crontab_time, position) 
            values ('%s', '%s', %d, %d, %d, %d, %d, %d)
            """ % params

        else:
            params = (log_name, site_name, read_time, crontab_time, position)

            sql = """
            insert into tb_param(log_name, site_name, read_time, crontab_time, position) 
            values ('%s', '%s', %d, %d, %d)
            """ % params

        try:
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
            conn.rollback()


def remind_to_mysql(params):
    """
    报错提醒数据存储数据库
    """
    # 设置相关数据
    site_name = params[0]
    date_time = params[1]
    host = params[2]
    url = params[3]
    message = params[4]
    params = (site_name, date_time, host, url, message)

    sql = """
    insert into tb_remind(site_name, date_time, host, url, message) 
    values ('%s', '%s', '%s', '%s', '%s')
    """ % params

    # 保存到数据库
    try:
        cursor.execute(sql)
        conn.commit()
    except Exception as e:
        print(e)
        conn.rollback()


def position_to_mysql(log_name, position):
    """
    将当前读取的位置存储到数据库
    """
    sql = "update tb_param set position='%s' where log_name='%s'" % (position, log_name)

    try:
        cursor.execute(sql)
        conn.commit()
    except Exception as e:
        print(e)
        conn.rollback()


def overview_data_to_mysql(log_name):
    """
    统计日志数据, 存储数据库
    """
    if 'error' in log_name:
        tb_name = 'tb_error_log'
    else:
        tb_name = 'tb_access_log'

    sql2 = """
    SELECT SUM(num) 
    FROM(
    SELECT count(DISTINCT ip) AS num, DATE_FORMAT(date_time, '%Y-%m-%d %H:00:00') AS date_hour 
    FROM {} 
    GROUP BY date_hour) 
    AS tb_uv;""".format(tb_name)
    sql3 = "SELECT COUNT(DISTINCT ip) FROM {};".format(tb_name)

    sql1 = "SELECT COUNT(*) FROM {};".format(tb_name)

    sql4 = "SELECT MAX(num), date_time FROM (SELECT COUNT(*) as num, date_time FROM {} GROUP BY date_time) as tb_num;".format(
        tb_name)
    sql5 = "SELECT ROUND(AVG(num)) FROM (SELECT COUNT(*) as num, date_time FROM {} GROUP BY date_time) as tb_num;".format(
        tb_name)

    pv_count, uv_count, iv_count, max_second_requests, peak_time, avg_second_requests = 0, 0, 0, 0, None, 0

    try:
        cursor.execute(sql1)
        pv_count = cursor.fetchone()[0]
        cursor.execute(sql2)
        uv_count = cursor.fetchone()[0]
        cursor.execute(sql3)
        iv_count = cursor.fetchone()[0]
        cursor.execute(sql4)
        max_second_requests, peak_time = cursor.fetchone()
        cursor.execute(sql5)
        avg_second_requests = cursor.fetchone()[0]
    except Exception as e:
        print(e)

    # print(pv_count, uv_count, iv_count, max_second_requests, peak_time, avg_second_requests)

    params = (pv_count, uv_count, iv_count, max_second_requests, peak_time, avg_second_requests, log_name)
    sql5 = """
    UPDATE tb_param 
    SET pv_count='%d', uv_count='%d', iv_count='%d', max_second_requests='%d', peak_time='%s', avg_second_requests='%d' 
    WHERE log_name='%s';
    """ % params

    try:
        cursor.execute(sql5)
        conn.commit()
    except Exception as e:
        print(e)
        conn.rollback()


def get_log_names():
    """
    获取日志文件名称列表
    """
    # 目录文件夹生成器
    dir_gen = os.walk(directory)
    # 需要读取的日志文件
    temp_log_names = ["lbcwx.lingxiu.top.log", "lbcwx.lingxiu.top.error.log"]
    # 目录下文件名列表
    log_names = [log_name for log_name in [log_list for log_list in dir_gen][0][2] if
                 log_name != ".gitkeep" and log_name in temp_log_names]

    return log_names


def get_log_param(log_name):
    """
    获取日志文件当前读取的位置
    """
    error_count, error_time, remind_time, position = 10, 10, 10, 0
    if 'error' in log_name:
        sql = "select error_count, error_time, remind_time, position from tb_param where log_name='%s'" % (log_name,)
        try:
            cursor.execute(sql)
            error_count, error_time, remind_time, position = cursor.fetchone()
        except Exception as e:
            print(e)
        return error_count, error_time, remind_time, position
    else:
        sql = "select position from tb_param where log_name='%s'" % (log_name,)
        try:
            cursor.execute(sql)
            position = cursor.fetchone()[0]
        except Exception as e:
            print(e)
        return position


def get_site_data(log_name):
    """
    根据日志文件名称, 从数据库获取对应站点数据
    """
    site_id, site_name, host = None, None, None
    if 'error' in log_name:
        sql = "select site_id, site_name, host from tb_site where error_log='%s'" % (log_name,)
    else:
        sql = "select site_id, site_name, host from tb_site where access_log='%s'" % (log_name,)
    try:
        cursor.execute(sql)
        site_id, site_name, host = cursor.fetchone()
    except Exception as e:
        print(e)
    return site_id, site_name, host


def get_table_data_to_mysql(log_name):
    """
    获取日志概览页面列表数据
    """
    if 'error' in log_name:
        tb_name = 'tb_error_log'
    else:
        tb_name = 'tb_access_log'

    sql1 = """SELECT pv_count FROM tb_param WHERE log_name='{}';""".format(log_name)

    sql2 = """SELECT url, COUNT(*) AS num FROM {} GROUP BY url ORDER BY num DESC LIMIT 10;""".format(tb_name)

    pv_count, record_tuple = None, None
    try:
        cursor.execute(sql1)
        pv_count = cursor.fetchall()[0][0]
        cursor.execute(sql2)
        record_tuple = cursor.fetchall()
    except Exception as e:
        print(e)

    if not pv_count:
        pv_count = 1000000

    # print(pv_count)
    for record in record_tuple:
        # print(record[1], pv_count)
        percent = '{:.2%}'.format(record[1] / pv_count)

        sql3 = """
        SELECT MAX(num), date_time
        FROM (SELECT date_time, COUNT(*) as num FROM {} WHERE(url='{}') GROUP BY date_time) as tb_num;
        """.format(tb_name, record[0])

        result = None
        try:
            cursor.execute(sql3)
            result = cursor.fetchone()
        except Exception as e:
            print(e)
        if result:
            temp_tuple = (percent, result[0], result[1].strftime('%Y-%m-%d %H:%M:%S'), log_name)
            record = record + temp_tuple
            # print(record)

        temp_url = None
        sql4 = "select * from tb_url_record where (url='%s' and log_name='%s');" % (record[0], log_name)
        try:
            cursor.execute(sql4)
            temp_url = cursor.fetchone()
        except Exception as e:
            print(e)

        # 如果 url 在 tb_url_record 中, 则更新数据
        if temp_url:
            sql5 = """update tb_url_record set pv_count='%d', percent='%s', peak_count='%d', peak_time='%s' 
            where (url='%s' and log_name='%s');""" % (record[1], record[2], record[3], record[4], record[0], record[5])
        # 如果 url 不在 tb_url_record 中, 直接插入数据
        else:
            sql5 = """INSERT INTO tb_url_record(url, pv_count, percent, peak_count, peak_time, log_name) 
            values ('%s', '%d', '%s', '%d', '%s', '%s')""" % record
        # 保存到数据库
        try:
            cursor.execute(sql5)
            conn.commit()
        except Exception as e:
            print(e)
            conn.rollback()


def get_time_pv_data(log_name, temp_time, temp_num):
    """
    获取指定日期的不同时间段的访问量
    """
    if "error" in log_name:
        tb_name = "tb_error_log"
    else:
        tb_name = "tb_access_log"

    n = int(24 / temp_num)  # 时间分段 24或48

    cur_time = datetime.datetime.strptime(temp_time, '%Y-%m-%d')  # 字符串转日期格式
    # print(cur_time)

    pv_data = []

    for i in range(0, n):
        # 开始时间
        start_time = (cur_time + datetime.timedelta(minutes=60 * temp_num * i)).strftime("%Y-%m-%d %H:%M")
        # 结束时间
        end_time = (cur_time + datetime.timedelta(minutes=60 * temp_num * (i + 1))).strftime("%Y-%m-%d %H:%M")
        # 时间段的时间点
        time_point = (cur_time + datetime.timedelta(minutes=60 * temp_num * i)).strftime("%H:%M")

        sql = """
        SELECT COUNT(*)
        FROM {}
        WHERE date_time BETWEEN '{}' AND '{}';
        """.format(tb_name, start_time, end_time)

        result = None
        try:
            cursor.execute(sql)
            result = cursor.fetchall()[0]
        except Exception as e:
            print(e)

        if result:
            data_dict = {"Time": time_point, "Count": result[0]}
            pv_data.append(data_dict)

    # print(pv_data)
    return pv_data


def get_time_uv_data(log_name, temp_time):
    """
    获取指定日期的不同时间段(每小时)的访客量
    """
    if "error" in log_name:
        tb_name = "tb_error_log"
    else:
        tb_name = "tb_access_log"

    temp_time = datetime.datetime.strptime(temp_time, '%Y-%m-%d')  # 字符串转日期格式

    uv_data = []
    for i in range(0, 24):
        # 开始时间
        start_time = (temp_time + datetime.timedelta(hours=1 * i)).strftime("%Y-%m-%d %H:00:00")
        # print(start_time)

        # 结束时间
        end_time = (temp_time + datetime.timedelta(hours=1 * (i + 1))).strftime("%Y-%m-%d %H:00:00")
        # print(end_time)

        # 时间段的时间点
        time_point = (temp_time + datetime.timedelta(hours=1 * i)).strftime("%H:00")

        sql = """
        SELECT count(DISTINCT ip) AS num
        FROM {}
        WHERE date_time BETWEEN '{}' AND '{}'
        """.format(tb_name, start_time, end_time)

        result = None
        try:
            cursor.execute(sql)
            result = cursor.fetchall()[0]
        except Exception as e:
            print(e)
        # print(results)

        if result:
            data_dict = {"Time": time_point, "Count": result[0]}
            uv_data.append(data_dict)

    # print(uv_data)
    return uv_data


# def get_chart_data(log_name):
#     """
#     获取日志概览页面图表数据
#     """
#     if 'error' in log_name:
#         tb_name = 'tb_error_log'
#     else:
#         tb_name = 'tb_access_log'
#
#     # 开发
#     # 当前时间
#     current_time = '2019-09-03 16:12:06'
#     # 格式化当前时间
#     cur_time = datetime.datetime.strptime(current_time, '%Y-%m-%d %H:%M:%S')
#
#     # 测试
#     # current_time = datetime.datetime.now()
#     # # 日志读取有一个小时延迟
#     # cur_time = current_time - datetime.timedelta(hours=1)
#
#     hours_time = []  # 近一天24小时时间点, 按时间升序
#     for i in reversed(range(1, 25)):
#         temp_time = (cur_time - datetime.timedelta(hours=i)).strftime("%Y-%m-%d %H:00:00")
#         hours_time.append(temp_time)
#     # print(hours_time)
#
#     minutes_time = []  # 近一小时60分钟时间点, 按时间升序
#     for i in reversed(range(1, 61)):
#         temp_time = (cur_time - datetime.timedelta(minutes=i)).strftime("%Y-%m-%d %H:%M:00")
#         minutes_time.append(temp_time)
#     # print(minutes_time)
#
#     sql1 = """
#     SELECT DATE_FORMAT(date_time, '%Y-%m-%d %H:00:00') AS date_hour, COUNT(*)
#     FROM {}
#     WHERE date_time BETWEEN '2019-09-02 16:12:06' AND '2019-09-03 16:12:06'
#     GROUP BY date_hour
#     ORDER BY date_time;
#     """.format(tb_name)
#
#     sql2 = """
#     SELECT DATE_FORMAT(date_time, '%Y-%m-%d %H:%i:00') AS date_minute, COUNT(*)
#     FROM {}
#     WHERE date_time BETWEEN '2019-09-03 15:12:06' AND '2019-09-03 16:12:06'
#     GROUP BY date_minute
#     ORDER BY date_time;
#     """.format(tb_name)
#
#     result1, result2 = None, None
#     try:
#         cursor.execute(sql1)
#         result1 = cursor.fetchall()
#         cursor.execute(sql2)
#         result2 = cursor.fetchall()
#     except Exception as e:
#         print(e)
#
#     temp_hours_time = []  # 数据库查询到的24小时时间点
#     temp_hours_data = []  # 数据库查询小时时间点对应的数据
#     for result in result1:
#         temp_hours_time.append(result[0])
#         temp_hours_data.append(result[1])
#     # print(temp_hours_time)
#
#     hours_data = []  # 近一天24小时时间点, 对应访问量数据
#     for i in hours_time:
#         if i in temp_hours_time:
#             hours_data.append(temp_hours_data[0])
#             temp_hours_data.pop(0)
#         else:
#             hours_data.append(0)
#     # print(hours_data)
#
#     temp_minutes_time = []  # 数据库查询到的60分钟时间点
#     temp_minutes_data = []  # 数据库查询分钟时间点对应的数据
#     for result in result2:
#         temp_minutes_time.append(result[0])
#         temp_minutes_data.append(result[1])
#     # print(temp_minutes_time)
#     minutes_data = []  # 近一小时60分钟时间点, 对应访问量数据
#     for i in minutes_time:
#         if i in temp_minutes_time:
#             minutes_data.append(temp_minutes_data[0])
#             temp_minutes_data.pop(0)
#         else:
#             minutes_data.append(0)
#     # print(minutes_data)
#
#     return hours_time, hours_data, minutes_time, minutes_data
#
#
# def get_method_data():
#     """
#     获取请求方法数据
#     """
#     sql = """
#     SELECT method, COUNT(*) as num
#     FROM tb_access_log
#     GROUP BY method
#     ORDER BY num DESC;
#     """
#
#     result = None
#     try:
#         cursor.execute(sql)
#         result = cursor.fetchall()
#     except Exception as e:
#         print(e)
#
#     method_dict = dict(result)
#
#     method_names = []
#     method_values = []
#     for i in result:
#         method_names.append(i[0])
#         method_values.append(i[1])
#
#     return method_dict, method_names, method_values
#
#
# def get_status_data():
#     """
#     获取状态码数据
#     """
#     sql = """
#     SELECT status, COUNT(*) as num
#     FROM tb_access_log
#     GROUP BY status
#     ORDER BY num DESC;
#     """
#
#     result = None
#     try:
#         cursor.execute(sql)
#         result = cursor.fetchall()
#     except Exception as e:
#         print(e)
#
#     status_dict = dict(result)
#
#     status_names = []
#     status_values = []
#     for i in result:
#         status_names.append(i[0])
#         status_values.append(i[1])
#
#     return status_dict, status_names, status_values


def pv_uv_data_to_mysql(log_name):
    """
    临时访问量, 访客量 存储数据库, 更新参数表
    """
    # 查询日志文件对应参数表的日期标记
    sql1 = """SELECT flag_time FROM tb_param WHERE log_name='{}'""".format(log_name)
    flag_time = None
    try:
        cursor.execute(sql1)
        flag_time = cursor.fetchone()[0]
    except Exception as e:
        print(e)
        conn.rollback()

    # 将前一日的日志数据存储数据库(访问量:pv_data, 访客量:uv_data)
    cur_time = datetime.datetime.now().strftime("%Y-%m-%d")
    # 前一天日期
    temp_time = (datetime.datetime.strptime(cur_time, '%Y-%m-%d') - datetime.timedelta(days=1)).strftime(
        "%Y-%m-%d")

    # 当前日期的前一天日期 与 临时日期 不同, 重新查询存储数据
    if temp_time != flag_time:
        # 将日期标记改为前一天日期
        flag_time = temp_time

        pv_data_hour = str(get_time_pv_data(log_name, temp_time, temp_num=1)).replace("\'", "\"")
        pv_data_half = str(get_time_pv_data(log_name, temp_time, temp_num=0.5)).replace("\'", "\"")
        uv_data = str(get_time_uv_data(log_name, temp_time)).replace("\'", "\"")

        params = (flag_time, pv_data_hour, pv_data_half, uv_data, log_name)
        sql = """
        UPDATE tb_param 
        SET flag_time='%s', pv_data_hour='%s', pv_data_half='%s', uv_data='%s'
        WHERE log_name='%s';
        """ % params

        try:
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
            conn.rollback()


def ding_remind(msg, mobiles):
    """
    钉钉群机器人 站点报错提醒
    """
    # WebHook地址
    webhook = '''https://oapi.dingtalk.com/robot/send?access_token=73943f3fe551d4f760dcf827986cd19570ec660505b02a4846c4e8567f9a6a41'''

    # 初始化机器人
    ding_remind = DingtalkChatbot(webhook)

    if mobiles:
        # Text消息之@指定用户
        at_mobiles = mobiles  # 值班人员手机号码
        ding_remind.send_text(msg=msg, at_mobiles=at_mobiles)
    else:
        # Text消息之@所有人
        ding_remind.send_text(msg=msg, is_at_all=True)


def crontab_task(task_name):
    """
    定时任务
    """
    log_names = get_log_names()

    # log_names = ["lbcwx.lingxiu.top.error.log"]
    # print(log_names)  # 所有日志文件名称
    for log_name in log_names:
        # 开发
        schedule.every().minute.do(task_name, log_name)  # 每隔一分钟执行一次日志读取定时任务
        # 测试
        # schedule.every().hour.do(task_name, log_name)  # 每隔一小时执行一次日志读取定时任务

    while True:
        # 保持schedule一直运行，然后去查询上面的任务
        schedule.run_pending()


def ping_link(link):
    """
    读取日志文件前, ping域名判断是否连通, 返回结果
    """
    # windows ping 域名
    result = os.system("ping {} -n 1".format(link))

    # Linux ping 域名
    # result = os.system("ping {} -c 1 -n".format(link))

    if not result:
        # result = 0, 表示能ping通
        return True
    else:
        # result = 1, 表示ping不通
        return False


if __name__ == '__main__':
    crontab_task(read_logs)
